package com.rybbaby.tss.core.utils;


import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import net.sf.json.JsonConfig;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.lang.reflect.Field;
import java.nio.charset.StandardCharsets;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtil {

    /**
     * 方法 导入excel
     *
     * @param cls        泛型对象
     * @param xlsPath    Excel对象的路径
     * @param importJson json文件
     * @param <T>        泛型
     * @return
     * @throws IOException
     */
    public static <T> List<T> loadExcel(Class<T> cls, String xlsPath, File importJson) throws IOException, InvalidFormatException {
        List<T> temp = new ArrayList<>();
        FileInputStream inputStream = new FileInputStream(xlsPath);
        //根据指定的文件输入流导入Excel产生workbook对象
        Workbook workbook = null;
        if (inputStream.toString().endsWith("xls")) {
            workbook = new HSSFWorkbook(inputStream);
        } else {
            workbook = new XSSFWorkbook(inputStream);
        }
        Sheet sheet = workbook.getSheetAt(0);

        //获取第一行的行号num
        int firstRowNum = sheet.getFirstRowNum();
        //根据行号取得第一行
        Row firstRow = sheet.getRow(firstRowNum);
        //取得第一行的第一列和最后一列
        short firstCellNum = firstRow.getFirstCellNum();
        short lastCellNum = firstRow.getLastCellNum();

        Map<String, String> columnNameMapping = new TreeMap<String, String>();
        Map<String, List<ExcelDictionary>> dictionaryMapping = new TreeMap<String, List<ExcelDictionary>>();
        Map<String, String> columnTypeMapping = new HashMap<String, String>();
        List<ExcelHeards> excelHeards = readConfigJson(importJson);
        List<ExcelDictionary> dictionary = null;
        for (ExcelHeards excelHeard : excelHeards) {
            String javaField = excelHeard.getJavaField();
            columnNameMapping.put(javaField, excelHeard.getColumnName());
            dictionary = excelHeard.getDictionary();
            dictionaryMapping.put(javaField, dictionary);
            columnTypeMapping.put(javaField, excelHeard.getColumnType());
        }

        Map<String, Integer> fieldIndex = new TreeMap<String, Integer>();
        Field[] declaredFields = cls.getDeclaredFields();// 获取属性
        for (Field field : declaredFields) {
            String fieldName = field.getName();// id ,name ,sex,age
            if (StringUtils.isNotBlank(fieldName)) {
                String columnName = columnNameMapping.get(fieldName);
                for (int i = firstCellNum; i < lastCellNum; i++) {
                    Cell cell = firstRow.getCell(i);
                    String realColumnName = cell.getStringCellValue();
                    if (realColumnName.equals(columnName)) {
                        fieldIndex.put(fieldName, i);
                    }
                }
            }
        }

        for (Row r : sheet) {
            if (r.getRowNum() < 1) {
                continue;
            }
            try {
                T t = cls.newInstance();
                Field[] declaredFields1 = cls.getDeclaredFields();// 获取属性
                for (Field field : declaredFields1) {
                    field.setAccessible(true);
                    String fieldName = field.getName();
                    Integer index = fieldIndex.get(fieldName);
                    if (index == null) {
                        continue;
                    }
                    Cell cell = r.getCell(index);
                    Class<?> type = field.getType();
                    String typeName = type.getTypeName();
                    String columnName = columnTypeMapping.get(fieldName);
                    typeName = StringUtils.isBlank(columnName) ? typeName : columnName;
                    Object fieldValue = null;
                    boolean isSetValue = false;
                    switch (typeName) {
                        case "java.lang.String":
                            if( cell == null ){
                                fieldValue="";
                            }else {
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                fieldValue = cell.getStringCellValue();
                            }
                            break;
                        case "java.lang.Boolean":
                            if( cell==null ){
                                fieldValue=false;
                            }else {
                                cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
                                fieldValue = cell.getBooleanCellValue();
                            }
                            break;
                        case "java.lang.Integer":
                            if(cell==null){
                                fieldValue=0;
                            }else {
                                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                                fieldValue = (int) cell.getNumericCellValue();
                            }
                            break;
                        case "java.lang.Short":
                            if(cell==null){
                                fieldValue=(short)0;
                            }else {
                                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                                fieldValue = (short) cell.getNumericCellValue();
                            }
                            break;
                        case "java.lang.Double":
                            if(cell==null){
                                fieldValue=0;
                            }else {
                                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                                fieldValue = cell.getNumericCellValue();
                            }
                            break;
                        case "java.lang.Float":
                            if(cell==null){
                                fieldValue=0;
                            }else {
                                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                                fieldValue = cell.getNumericCellValue();
                            }
                            break;
                        case "java.lang.Long":
                            if(cell==null){
                                fieldValue=0L;
                            }else {
                                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                                fieldValue = (long) cell.getNumericCellValue();
                            }
                            break;
                        case "java.util.Date":
                        case "java.sql.Timestamp":
                            Date date = null;
                            try {
                                date = cell.getDateCellValue();
                            } catch (Exception e) {
                                date = DateUtil.parse(cell.getStringCellValue(), "yyyy-MM-dd HH:mm:ss");
                            }
                            try {
                                field.set(t, date);
                            } catch (Exception e) {
                                field.set(t, new Timestamp(date.getTime()));
                            }
                            isSetValue = true;
                            break;
                    }

                    List<ExcelDictionary> dictionaries = dictionaryMapping.get(fieldName);
                    if (dictionaries != null && !dictionaries.isEmpty()) {
                        for (ExcelDictionary dictionary1 : dictionaries) {
                            String chinese = dictionary1.getChinese();
                            if (chinese.equals(fieldValue)) {
                                fieldValue = dictionary1.getCode();
                            }
                        }
                    }
                    if (!isSetValue) {
                        if(fieldValue!=null) {
                            if (Integer.class.equals(type) && fieldValue.getClass().equals(String.class)) {
                                fieldValue = Integer.parseInt(com.rybbaby.tss.core.utils.StringUtils.isNotBlank(fieldValue) ? fieldValue.toString() : "0");
                            }
                        }
                        field.set(t, fieldValue);
                    }
                }
                temp.add(t);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        inputStream.close();
        return temp;
    }

    /**
     * 导出Excel
     *
     * @param data           数据源list
     * @param filePath       导出Excel位置
     * @param exportJsonFile 需要导出属性的json文件
     * @param <T>            泛型
     * @return
     * @throws Exception
     */
    public static <T> Workbook exportExcel(List<T> data, File exportJsonFile) throws Exception {
        //创建一个Excel
        XSSFWorkbook hwb = new XSSFWorkbook();
        //创建会员表
        XSSFSheet sheet = hwb.createSheet(getSheetName(exportJsonFile));
        //设置列宽自适应
        sheet.autoSizeColumn(1, true);
        //设置对齐方式
        XSSFCellStyle style = hwb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //创建导出的文件
        Iterator<T> it = data.iterator();

        int rowIndex = 1;
        try {
            //创建第一行
            XSSFRow row0 = sheet.createRow(0);
            List<ExcelHeards> excelHeards = readConfigJson(exportJsonFile);
            List<ExcelDictionary> dictionaries = null;
            Map<String, List<ExcelDictionary>> dictionaryMapping = new TreeMap<String, List<ExcelDictionary>>();
            int cellIndex = 0;
            for (ExcelHeards excelHeard : excelHeards) {
                row0.createCell(cellIndex++).setCellValue(excelHeard.getColumnName());
                String javaField = excelHeard.getJavaField();
                dictionaries = excelHeard.getDictionary();
                dictionaryMapping.put(javaField, dictionaries);
            }

            while (it.hasNext()) {
                int rowNum = rowIndex++;
                //创建其他行（list集合元素个数确定）
                XSSFRow row = sheet.createRow(rowNum);
                T t = (T) it.next();
                Class<?> aClass = t.getClass();

                cellIndex = 0;
                String fieldName = "";
                for (ExcelHeards excelHeard : excelHeards) {
                    Object value = null;
                    try {
                        Field field = aClass.getDeclaredField(excelHeard.getJavaField());
                        field.setAccessible(true);
                        fieldName = field.getName();
                        //columnName = field.getName();
                        value = field.get(t);
                    } catch (Exception e) {
                    }
                    XSSFCell cell = row.createCell(cellIndex++);

                    //类型判断
                    String textValue = null;
                    //日期格式
                    if (value instanceof Date || value instanceof Timestamp) {
                        Date date = (Date) value;
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        textValue = sdf.format(date);
                    } else {
                        if (value == null) {
                            value = "";
                        } else {
                            List<ExcelDictionary> excelDictionaries = dictionaryMapping.get(fieldName);
                            if (excelDictionaries != null && !excelDictionaries.isEmpty()) {
                                for (ExcelDictionary dictionary : excelDictionaries) {
                                    if (dictionary.getCode().equals(value)) {
                                        String chinese = dictionary.getChinese();
                                        textValue = dictionary.getChinese();
                                        break;
                                    }
                                }
                            } else {
                                textValue = value.toString();
                            }
                        }
                    }
                    if (StringUtils.isNotBlank(textValue)) {
                        if (value instanceof Number) {
                            cell.setCellValue(textValue);
                        } else {
                            cell.setCellValue(textValue);
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("导出完成");
        return hwb;
    }


    /**
     * 方法 导入Excel 读取json文件
     *
     * @param jsonConfig json文件
     * @return
     */
    public static List<ExcelHeards> readConfigJson(File jsonConfig) {
        List<ExcelHeards> heardss = new ArrayList<ExcelHeards>();
        try (FileInputStream input = new FileInputStream(jsonConfig)) {
            String jsonContext = IOUtils.toString(input, StandardCharsets.UTF_8);
            JSONObject object = JSONObject.fromObject(jsonContext);
            JSONArray heards = object.getJSONArray("heards");
            for (int j = 0; j < heards.size(); j++) {
                JSONObject jsonObject = heards.getJSONObject(j);
                JsonConfig jsonConfig1 = new JsonConfig();
                jsonConfig1.setRootClass(ExcelHeards.class);
                Map<String, Class> classMap = new HashMap<String, Class>();
                classMap.put("dictionary", ExcelDictionary.class);
                jsonConfig1.setClassMap(classMap);
                heardss.add(JSONUtil.jsonToBean(jsonObject, jsonConfig1));
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return heardss;
    }


    /**
     * 方法 导出Excel 获取json sheetName值
     *
     * @param exportJson json文件
     * @return
     */
    public static String getSheetName(File exportJson) {
        String sheetName = null;
        try (FileInputStream input = new FileInputStream(exportJson)) {
            String jsonContext = IOUtils.toString(input);
            JSONObject object = JSONObject.fromObject(jsonContext);
            sheetName = object.getString("sheetName");
        } catch (IOException e) {
            e.printStackTrace();
        }
        return sheetName;
    }

    public static class ExcelHeards {
        /**
         * excel标题
         **/
        private String columnName;

        /**
         * 对应的java属性
         **/
        private String javaField;

        /**
         * 属性对应的数字字典
         */
        private List<ExcelDictionary> dictionary;

        private String columnType;

        public List getDictionary() {
            return dictionary;
        }

        public void setDictionary(List<ExcelDictionary> dictionary) {
            this.dictionary = dictionary;
        }

        public String getColumnName() {
            return columnName;
        }

        public void setColumnName(String columnName) {
            this.columnName = columnName;
        }

        public String getJavaField() {
            return javaField;
        }

        public void setJavaField(String javaField) {
            this.javaField = javaField;
        }

        public String getColumnType() {
            return columnType;
        }

        public void setColumnType(String columnType) {
            this.columnType = columnType;
        }
    }


    /**
     * 数字字典
     */
    public static class ExcelDictionary {

        /**
         * 數字字典的数字
         */
        private Object code;

        /**
         * 数字对应的中文
         */
        private String chinese;

        public Object getCode() {
            return code;
        }

        public void setCode(Object code) {
            this.code = code;
        }

        public String getChinese() {
            return chinese;
        }

        public void setChinese(String chinese) {
            this.chinese = chinese;
        }
    }

    /**
     *@Author Created by zhang
     *
     *@Date 2017/8/17 11:55
     */
    public static String readInputStream(InputStream inputStream) {
        BufferedReader reader = null;
        String laststr = "";
        try {
            InputStreamReader inputStreamReader = new InputStreamReader(inputStream, "UTF-8");
            reader = new BufferedReader(inputStreamReader);
            String tempString = null;
            while ((tempString = reader.readLine()) != null) {
                laststr += tempString;
            }
            reader.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return laststr;
    }
}
